package org.agile.grenoble.data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.agile.grenoble.questions.AnswerType;
import org.agile.grenoble.questions.ConfigurationType;
import org.agile.grenoble.questions.QuestionType;
import org.agile.grenoble.questions.QuestionsType;
import org.agile.grenoble.user.User;

import com.mysql.jdbc.exceptions.MySQLSyntaxErrorException;

//import javax.swing.JTextField;
public class AnswersStorage implements IAnswersStorage, IUserStorage {

  private static String host = Configuration.getString("AnswersStorage.dbhost"); //$NON-NLS-1$
  private static int port = Configuration.getInt("AnswersStorage.port");
  private static String user = Configuration.getString("AnswersStorage.user"); //$NON-NLS-1$
  private static String password = Configuration.getString("AnswersStorage.password"); //$NON-NLS-1$
  private static String database = Configuration.getString("AnswersStorage.database"); //$NON-NLS-1$
  Connection conn = null;
  int schemaVersion = 3;
  int schemaUserVersion = 3;

  private Connection getConnection() {
    if (conn == null) {
      conn = createConnection();
      return conn;
    } else {
      return conn;
    }
  }

  private Connection createConnection() {
    Connection conn = null;
    try {
      Class.forName(Configuration.getString("AnswersStorage.drivername")).newInstance(); //$NON-NLS-1$
      conn = DriverManager.getConnection(Configuration.getString("AnswersStorage.protocol") + host + ":" + port + "/" + database + "?" + //$NON-NLS-1$
        "user=" + user + "&password=" + password);
    } catch (SQLException e) {
      // handle any errors of type SQL
      System.out.println("SQLException: " + e.getMessage());
      System.out.println("SQLState: " + e.getSQLState());
      System.out.println("VendorError: " + e.getErrorCode());
    } catch (InstantiationException e) {
      System.out.println("SQLException: " + e.getMessage());
      e.printStackTrace();
    } catch (IllegalAccessException e) {
      System.out.println("SQLException: " + e.getMessage());
      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      System.out.println("SQLException: " + e.getMessage());
      e.printStackTrace();
    }
    return conn;
  }

  /* (non-Javadoc)
   * @see org.agile.grenoble.data.IStorage#initializeDB(org.agile.grenoble.questions.QuestionsType)
   */
  public void initializeDB(QuestionsType questions) throws StorageException {
    Connection conn = getConnection();
    try {
      if (conn != null) {
        Statement stat = conn.createStatement();
        ResultSet res = null;
        try {
          res = stat.executeQuery("select * from NokiaTest.versions order by anId");
        } catch (MySQLSyntaxErrorException e) {
          System.out.println("No result: " + e);
          //e.printStackTrace();
        }
        if (res == null) {
          stat.close();
          initDB(conn, questions);
        } else {
          System.out.println("Up2date table, doing nothing");
          //TODO we should check the version installed of the schema
          res.close();
          stat.close();
        }
      }
    } catch (SQLException sqlex) {
      throw new StorageException(sqlex);
    }
  }

  @Override
  public void initializeUserDb() throws StorageException {
    try {
      Connection conn = getConnection();
      Statement stat = conn.createStatement();
      ResultSet res = null;
      try {
        res = stat.executeQuery("select * from NokiaTest.userVersions order by anId");
      } catch (MySQLSyntaxErrorException e) {
        System.out.println("No result: " + e);
        //e.printStackTrace();
      }
      if (res != null) {
        return;
      }
      boolean res2 = createSurveyTable(stat);
      if (res2) {
        System.out.println("Creation of survey table  succeed");
      }
      stat.close();
      stat = conn.createStatement();
      res2 &= createUserVersionTable(stat);
      if (res2) {
        System.out.println("Creation of version table  succeed");
      }
      stat.close();
    } catch (SQLException sqlex) {
      throw new StorageException(sqlex);
    }
  }

  private void initDB(Connection conn, QuestionsType questions) throws SQLException {
    Statement stat = conn.createStatement();
    ResultSet res = null;
    try {
      res = stat.executeQuery("select * from NokiaTest.versions order by anId");
    } catch (MySQLSyntaxErrorException e) {
      System.out.println("No result: " + e);
      //e.printStackTrace();
    }
    if (res != null) {
      return;
    }
    stat.close();
    stat = conn.createStatement();
    boolean res2 = createVersionTable(stat);
    //add the ifnot exist, as the installation may be incremental
    //TODO generate schema from table definition ... simple/complex....
    for (int i = 0; i < questions.getQuestionArray().length; i++) {
      res2 &= createTableForQuestions(stat, questions.getQuestionArray(i), i);
    }
    if (res2) {
      System.out.println("Creation of table surveys succeed");
    } else {
      System.out.println("Creation of table surveys failed");
    }
    stat.close();
  }

  private int getNbFields(QuestionType question) {
    int nb = 0;
    if (question.getConfiguration().getType() == ConfigurationType.Type.SINGLE) {
      nb = 1;
    } else if (question.getConfiguration().getType() == ConfigurationType.Type.MULTIPLE) {
      nb = question.getConfiguration().getNumber();
    } else {
      //complex 
      for (int i = 0; i < question.getQuestionArray().length; i++) {
        QuestionType child = question.getQuestionArray(i);
        nb += getNbFields(child);
      } //end for 
    } //end if 

    return nb;
  }

  private boolean createSurveyTable(Statement stat) throws SQLException {
    boolean res = stat.execute("Drop table IF EXISTS NokiaTest.surveys");
    System.out.println("Drop table (survey) status " + res);
    res &= stat.execute(
      "Create table NokiaTest.surveys (anId INT AUTO_INCREMENT UNIQUE, aName VARCHAR(56), anEmail VARCHAR(56), aDate TIMESTAMP)");
    return res;
  }

  private boolean createVersionTable(Statement stat) throws SQLException {
    boolean res = stat.execute("Drop table IF EXISTS NokiaTest.versions");
    System.out.println("Drop table  (version) status " + res);
    res &= stat.execute(
      "Create table NokiaTest.versions (anId INT AUTO_INCREMENT UNIQUE, aVersion VARCHAR(56), aDate TIMESTAMP)");
    System.out.println("Create table status " + res);
    res &= stat.execute("insert into NokiaTest.versions (aVersion) values (" + schemaVersion + ")");
    System.out.println("Version insertion status " + res);
    return res;
  }

  private boolean createUserVersionTable(Statement stat) throws SQLException {
    boolean res = stat.execute("Drop table IF EXISTS NokiaTest.userVersions");
    System.out.println("Drop table  (version) status " + res);
    res &= stat.execute(
      "Create table NokiaTest.userVersions (anId INT AUTO_INCREMENT UNIQUE, aVersion VARCHAR(56), aDate TIMESTAMP)");
    System.out.println("Create table status " + res);
    res &= stat.execute(
      "insert into NokiaTest.userVersions (aVersion) values (" + schemaUserVersion + ")");
    System.out.println("Version insertion status " + res);
    return res;
  }

  private boolean createTableForQuestions(Statement stat, QuestionType question, int indice)
    throws SQLException {

    String fields = "";
    boolean res = stat.execute("Drop table IF EXISTS NokiaTest.question_" + indice);
    if (!res) {
      System.out.println("failed to drop table question_" + indice);
    }
    int nb = getNbFields(question);
    for (int i = 0; i < nb; i++) {
      if (i > 0) {
        fields += ",";
      }
      fields += "anAnswers_" + i + " INT";
    } //end for  
    String query = "Create table  IF NOT EXISTS NokiaTest.question_" + indice + " (anId INT UNIQUE, " + fields + ")";
    System.out.println("Query is : " + query);
    res = stat.execute(query);
    return res;
  }

  /* (non-Javadoc)
   * @see org.agile.grenoble.data.IStorage#getorCreateUser(java.lang.String, java.lang.String)
   */
  /* (non-Javadoc)
   * @see org.agile.grenoble.data.IUserStorage#getorCreateUser(java.lang.String, java.lang.String)
   */
  @Override
  public User getorCreateUser(String pName, String pEmail) throws StorageException {
    try {
      Connection conn = getConnection();
      User user = null;
      if (conn != null) {
        Statement stat = conn.createStatement();
        ResultSet res = stat.executeQuery(
          "Select anId, aName, anEmail from nokiatest.surveys where aName='" + pName + "' ;");
        user = new User();
        if ((res != null) && res.next()) {
          user.setName(res.getString(2));
          user.setEmail(res.getString(3));
          user.setId(res.getInt(1));
        } else {
          System.out.println("No result, should create a new one");
          user = createUser(stat, pName, pEmail);
        }
        res.close();
        stat.close();

      } else {
        System.out.println("ERROR GETTING A CONNECTION ");
      } //end if 
      return user;
    } catch (SQLException sqlex) {
      throw new StorageException(sqlex);
    }
  }

  private User createUser(Statement stat, String pName, String pEmail) throws SQLException {
    boolean ok = stat.execute(
      "INSERT INTO nokiatest.surveys (aName,anEmail) VALUES ('" + pName + "','" + pEmail + "');");
    if (!ok) {
      System.out.println("Fail to add a new user");
    }
    User iUser = new User();
    iUser.setName(pName);
    iUser.setEmail(pEmail);
    ResultSet res = stat.executeQuery("SELECT LAST_INSERT_ID();");
    if (res.next()) {
      iUser.setId(res.getInt(1));
    } else {
      System.out.println("Can not get id generated for user ");
      iUser.setId(-1);
    }
    res.close();
    System.out.println("Create a user : " + pName + ", " + iUser.getEmail() + "," + iUser.getId());
    return iUser;
  }

  /* (non-Javadoc)
   * @see org.agile.grenoble.data.IStorage#storeAnswers(org.agile.grenoble.questions.QuestionsType, int)
   */
  @Override
  public void storeAnswers(QuestionsType questions, int pUserId) throws StorageException {
    //QuestionsType questions = doc.getQuestions() ;
    Connection conn = getConnection();
    try {
      if (conn != null) {
        Statement stat = conn.createStatement();
        boolean res = true;
        for (int i = 0; i < questions.getQuestionArray().length; i++) {
          res &= storePointForQuestions(stat, questions.getQuestionArray(i), pUserId, i);
        }
      } else {
        System.out.println("ERROR GETTING A CONNECTION ");
      } //end if 
    } catch (SQLException sqlex) {
      throw new StorageException(sqlex);
    }
  }

  private String getPoints(QuestionType question) {
    String points = null;
    if (question.getConfiguration().getType() == ConfigurationType.Type.SINGLE) {
      //
      points = getQuestionPoint(question);
      if (points == null) {
        points = "0";
      }
    } else if (question.getConfiguration().getType() == ConfigurationType.Type.MULTIPLE) {
      points = getQuestionPoint(question);
      if (points == null) {
        points = "0";
      }
    } else {
      //complex 
      for (int i = 0; i < question.getQuestionArray().length; i++) {
        QuestionType child = question.getQuestionArray(i);
        if (points != null) {
          points += "," + getPoints(child);
        } else {
          points = getPoints(child);
        }
      } //end for 
    } //end if 	
    return points;
  }

  /*
   * works for single and multiple ... we do some loop free ... but who cares ...
   */
  private String getQuestionPoint(QuestionType question) {
    String points = null;
    for (int i = 0; i < question.getAnswers().getAnswerArray().length; i++) {
      if (question.getAnswers().getAnswerArray(i).getSelected() == AnswerType.Selected.TRUE) {
        if (points != null) {
          points += ",";
        }
        points = question.getAnswers().getAnswerArray(i).getPoint();
      }
    }
    //System.out.println("We have update points for question : " + question.getLabel() + ", and we have found : " + points );
    return points;
  }

  private boolean storePointForQuestions(Statement stat, QuestionType question, int pUserId,
    int indice) throws SQLException {
    String fields = getPoints(question);
    String query = "Insert into NokiaTest.question_" + indice + " VALUES (" + pUserId + "," + fields + ") ; ";
    System.out.println("Query is : " + query);
    int res = stat.executeUpdate(query);
    System.out.println("We have update " + res + " rows");
    return (res == 1);
  }
}
